﻿using Lufei_CaterModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lufei_CaterDAL
{
    public class ProductDAL
    {

        /// <summary>
        /// 获取所有的商品
        /// </summary>
        /// <returns></returns>
        public List<ProductInfo> GetProducts()
        {
            string sql = "select * from productInfo where delflag=0";
            List<ProductInfo> products= new List<ProductInfo>();    

          DataTable dt=  SqlHelper.ExecuteDataTable(sql);

            if (dt.Rows.Count>0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    //关系转模型
                    products.Add(RowToProductInfo(dr));
                }
            }
            return products;
        }

        private ProductInfo RowToProductInfo(DataRow dr)
        {
            ProductInfo pro = new ProductInfo();
            pro.CatId = Convert.ToInt32(dr["CatId"]);
            // pro.DelFlag = Convert.ToInt32(dr["DelFlag"]);
            pro.ProCost = Convert.ToDecimal(dr["ProCost"]);
            pro.ProId = Convert.ToInt32(dr["ProId"]);
            pro.ProName = dr["ProName"].ToString();
            pro.ProNum = dr["ProNum"].ToString();
            pro.ProPrice = Convert.ToDecimal(dr["ProPrice"]);
            pro.ProSpell = dr["ProSpell"].ToString();
            pro.ProStock = Convert.ToDecimal(dr["ProStock"]);
            pro.ProUnit = dr["ProUnit"].ToString();
            pro.Remark = dr["Remark"].ToString();
            // pro.SubBy = Convert.ToInt32(dr["SubBy"]);
            // pro.SubTime = Convert.ToDateTime(dr["SubTime"]);
            return pro;
        }

        /// <summary>
        /// 根据id获取商品
        /// </summary>
        /// <returns></returns>
        public List<ProductInfo> GetProductInfoByID(int id)
        {
            string sql = "select * from productInfo where delflag=0 and catid="+ id;
            DataTable dt = SqlHelper.ExecuteDataTable(sql);

            List<ProductInfo> list = new List<ProductInfo>();
            if (dt.Rows.Count>0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    list.Add(RowToProductInfo(dr));
                }
            }
            return list;    
        }


        /// <summary>
        /// 拼音数字检索
        /// </summary>
        /// <param name="str">拼音</param>
        /// <param name="temp">数字标记位 1---拼音 2---数字</param>
        /// <returns></returns>
        public List<ProductInfo> GetProductInfoBySpellOrNum(string str,int temp)
        {
            string sql = "SELECT * FROM ProductInfo WHERE DelFlag = 0";
            if (temp==1)
            {
                //拼音
                sql += " AND ProSpell LIKE @str";
            }
            else if (temp==2)
            {
                sql += " AND ProNum LIKE @str";
            }

            SQLiteParameter param = new SQLiteParameter("@str","%"+str+"%");
            List<ProductInfo> list = new List<ProductInfo>();
           DataTable dt= SqlHelper.ExecuteDataTable(sql, param);

            if (dt.Rows.Count>0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    list.Add(RowToProductInfo(dr));
                }
            }
            return list;
        }


        /// <summary>
        /// 插入商品数据
        /// </summary>
        /// <param name="pi"></param>
        /// <returns></returns>
        public int InsertProduct(ProductInfo pi )
        {
            string sql = "INSERT INTO ProductInfo (CatId,ProName,ProCost,ProSpell,ProPrice,ProUnit,Remark,DelFlag,ProStock,ProNum) VALUES(@id,@name,@cost,@spell,@price,@unit,@remark,@delflag,@stock,@num)";

            SQLiteParameter[] param = new SQLiteParameter[]
            {
             new SQLiteParameter("@id",pi.CatId),
             new SQLiteParameter("@name",pi.ProName),
             new SQLiteParameter("@cost",pi.ProCost),
             new SQLiteParameter("@spell",pi.ProSpell),
             new SQLiteParameter("@price",pi.ProPrice),
             new SQLiteParameter("@unit",pi.ProUnit),
             new SQLiteParameter("@remark",pi.Remark),
             new SQLiteParameter("@delflag",pi.DelFlag),
             new SQLiteParameter("@stock",pi.ProStock),
             new SQLiteParameter("@num",pi.ProNum),
            };
          return  SqlHelper.ExecuteNonQuery(sql, param);
        }


        /// <summary>
        /// 修改商品
        /// </summary>
        /// <returns></returns>
        public int UpdateProduct(ProductInfo pi)
        {
            string sql = "UPDATE ProductInfo SET CatId=@id,ProName=@name,ProCost=@cost,ProSpell=@spell,ProPrice=@price,ProUnit=@unit,Remark=@remark,ProStock=@stock,ProNum=@num WHERE ProId=@ProId";
            SQLiteParameter[] param = new SQLiteParameter[]
        {
             new SQLiteParameter("@id",pi.CatId),
             new SQLiteParameter("@name",pi.ProName),
             new SQLiteParameter("@cost",pi.ProCost),
             new SQLiteParameter("@spell",pi.ProSpell),
             new SQLiteParameter("@price",pi.ProPrice),
             new SQLiteParameter("@unit",pi.ProUnit),
             new SQLiteParameter("@remark",pi.Remark),
             new SQLiteParameter("@stock",pi.ProStock),
             new SQLiteParameter("@num",pi.ProNum),
             new SQLiteParameter("@ProId",pi.ProId),
        };
            return SqlHelper.ExecuteNonQuery(sql, param);
        }

        /// <summary>
        /// 根据id获取一个商品
        /// </summary>
        /// <returns></returns>
        public ProductInfo GetProductInfoOneByID(int id)
        {
            string sql = "select * from productInfo where delflag=0 and ProId=" + id;
            DataTable dt = SqlHelper.ExecuteDataTable(sql);

            ProductInfo list = new ProductInfo();
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    list= RowToProductInfo(dr);
                }
            }
            return list;
        }


        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int ProductDelectById(int id)
        {
            string sql = "UPDATE ProductInfo set DelFlag = 1 WHERE ProId ="+id;
          return  SqlHelper.ExecuteNonQuery (sql);
        }

    }
}
